



ALTER             procedure  sp_insert_bankinfo_for_existing_salesorders

as
begin
	declare @salesOrder_Id int
        declare @bankName varchar(50)
        declare @address varchar(50)
	declare @city varchar(50)
	declare @state varchar(50)
	declare @zipcode varchar(50)
        declare @country varchar(50)
        declare @aba varchar(50)
	declare @acct varchar(50)
	declare @swiftcode varchar(50)
	declare @salesOrder_no varchar(50)
        declare @bankId int

	declare @results Table (SalesOrder_Id int, BankName varchar(50), Address varchar(50),City varchar(50), State varchar(50),ZipCode varchar(50), Country varchar(50), ABA varchar(50), Acct varchar(50), SwiftCode varchar(50))

		-- Added by sarada on 2nd March to get data from Client_Bankinfo table
		SELECT @bankId = BankId from Banking_Information

		SELECT @bankName = BankName , @address = Address , @city = City , @state = State , @zipcode = ZipCode , @country = Country , @aba = Aba , @acct = Acct , @swiftcode = SwiftCode 
		FROM Client_BankInfo where BankId = @bankId

		

		declare insert_orders CURSOR For
		SELECT distinct so.salesorder_id, so.salesorder_no 
		FROM sales_order so, customer_open_order_lineitem cool 
		WHERE cool.salesorder_id = so.salesorder_id and cool_status<>'SHIPPED' and cool.cool_is_cancel = 1 and SO_Show_Pro_Forma_Invoice = 0 and so.salesorder_id not in ( select DISTINCT SALESORDER_ID from customer_shipping_table  WHERE ( CST_STATUS = 'ISSUED' OR CST_STATUS = 'SHIPPED' ) ) order by so.salesorder_no
		
		
		open insert_orders		
		fetch next from  insert_orders into  @salesOrder_Id ,@salesOrder_no
		--print @salesOrder_Id
		while @@fetch_status = 0
		begin
						
				insert into Bank_Table (SalesOrder_Id , BankName , Address ,City , State ,ZipCode , Country , ABA , Acct , SwiftCode )
		 		values (@salesOrder_Id , @bankName , @address , @city , @state , @zipcode , @country , @aba , @acct , @swiftcode)
			
				fetch next from insert_orders into @salesOrder_Id,@salesOrder_no
		end 

		close insert_orders
		deallocate insert_orders

		declare insert_deleted_orders CURSOR For
		SELECT distinct SalesOrder_ID, SalesOrder_No 
		FROM Sales_Order WHERE Sales_Order.SalesOrder_ID not in (select SalesOrder_ID from customer_shipping_table) and Sales_Order.SO_Show_Pro_Forma_Invoice = 1 order by SalesOrder_No
		
		
		open insert_deleted_orders		
		fetch next from  insert_deleted_orders into  @salesOrder_Id ,@salesOrder_no
		--print @salesOrder_Id
		while @@fetch_status = 0
		begin
						
				insert into Bank_Table (SalesOrder_Id , BankName , Address ,City , State ,ZipCode , Country , ABA , Acct , SwiftCode )
		 		values (@salesOrder_Id , @bankName , @address , @city , @state , @zipcode , @country , @aba , @acct , @swiftcode)
			
				fetch next from insert_deleted_orders into @salesOrder_Id,@salesOrder_no
		end 

		close insert_deleted_orders
		deallocate insert_deleted_orders
   

   
	select * from Bank_Table
 

END








